﻿using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;


using LumenWorks.Framework.IO.Csv;
using OneMake.Model;
using OneMake.Excel;

namespace OneMake.DataAccess
{
    public class DataHelper
    {
        private ExcelHeleper eh = new ExcelHeleper();

        //数据导入接口
        public int ImportData(string table, string fromFile, int shopid, int fileid, DateTime BeginTime, DateTime EndTime)
        {
            FileInfo fi = new FileInfo(fromFile);
            if (fi.Extension.ToUpper() == ".CSV")
                return ImportCsvData(table, fromFile, shopid, fileid, BeginTime, EndTime);
            if (fi.Extension.ToUpper() == ".XLS")
                return ImportXlsData(table, fromFile, shopid, fileid, BeginTime, EndTime);
            if (fi.Extension.ToUpper() == ".XLSX")
                return ImportXlsData(table, fromFile, shopid, fileid, BeginTime, EndTime);

            return 0;
        }

        //导入Xls、Xlsx数据
        private int ImportXlsData(string table, string fromFile, int shopid, int fileid, DateTime BeginTime, DateTime EndTime)
        {
            int row = 0;
            string sheetName = eh.GetSheetName(fromFile, 0);
            string connStr = eh.GetConnString(fromFile);
            using (OleDbConnection dbConn = new OleDbConnection(connStr))
            {
                dbConn.Open();
                IDataReader reader = eh.ExecuteXlsReader(dbConn, sheetName);
                var db = new PetaPoco.Database("mysql" + UserSession.Network);

                using (var trans = db.GetTransaction())
                {
                    while (reader.Read())
                    {
                        Assembly asmb = Assembly.LoadFrom("OneMake.Domain.dll");
                        Type modelType = asmb.GetType(string.Format("OneMake.Model.{0}", table));
                        object model = asmb.CreateInstance(string.Format("OneMake.Model.{0}", table));

                        foreach (PropertyInfo info in modelType.GetProperties())
                        {
                            if (info.Name == "shopid")
                                info.SetValue(model, shopid, null);
                            else if (info.Name == "fileid")
                                info.SetValue(model, fileid, null);
                            else if (info.Name == "stat_datetime")
                                info.SetValue(model, BeginTime, null);
                            else
                            {
                                foreach (Attribute attr in info.GetCustomAttributes(true))
                                {
                                    MapToExcelAttribute mapToAttr = attr as MapToExcelAttribute;
                                    if (null != mapToAttr)
                                    {
                                        try
                                        {
                                            //object excelValue = reader[mapToAttr.ColumnName];
                                            int index = -1;
                                            for (index = 0; index < reader.FieldCount; index++)
                                            {
                                                if (reader.GetName(index).Trim() == mapToAttr.ColumnName.Trim())
                                                {
                                                    break;
                                                }
                                            }
                                            object excelValue = reader.GetValue(index);
                                            if (excelValue != DBNull.Value)
                                            {
                                                try
                                                {
                                                    if (info.PropertyType == typeof(double))
                                                    {
                                                        double dbValue = System.Math.Round((double)Convert.ChangeType(excelValue, info.PropertyType), 3);
                                                        info.SetValue(model, dbValue, null);
                                                    }
                                                    else
                                                    {
                                                        object dbValue = Convert.ChangeType(excelValue, info.PropertyType);
                                                        info.SetValue(model, dbValue, null);
                                                    }
                                                }
                                                catch (System.Exception ex)
                                                {
                                                    info.SetValue(model, 0, null);
                                                }
                                            }
                                        }
                                        catch (IndexOutOfRangeException ex)
                                        {
                                            throw new Exception("导入文件中找不到字段\"" + mapToAttr.ColumnName + "\"");
                                        }
                                    }
                                }
                            }
                        }
                        if (db.Insert(model) != null)
                            row++;
                    }
                    trans.Complete();
                }
            }
            return row;
        }

        //导入Csv数据
        private int ImportCsvData(string table, string fromFile, int shopid, int fileid, DateTime BeginTime, DateTime EndTime)
        {
            int row = 0;

            CsvReader reader = eh.ExecuteCsvReader(fromFile);
            var db = new PetaPoco.Database("mysql" + UserSession.Network);

            using (var trans = db.GetTransaction())
            {
                while (reader.ReadNextRecord())
                {
                    Assembly asmb = Assembly.LoadFrom("OneMake.Domain.dll");
                    Type modelType = asmb.GetType(string.Format("OneMake.Model.{0}", table));
                    object model = asmb.CreateInstance(string.Format("OneMake.Model.{0}", table));

                    foreach (PropertyInfo info in modelType.GetProperties())
                    {
                        if (info.Name == "shopid")
                            info.SetValue(model, shopid, null);
                        else if (info.Name == "fileid")
                            info.SetValue(model, fileid, null);
                        else if (info.Name == "stat_datetime")
                            info.SetValue(model, BeginTime, null);
                        else
                        {
                            foreach (Attribute attr in info.GetCustomAttributes(true))
                            {
                                MapToExcelAttribute mapToAttr = attr as MapToExcelAttribute;
                                if (null != mapToAttr)
                                {
                                    object excelValue = reader[mapToAttr.ColumnName];
                                    if (excelValue != DBNull.Value)
                                    {
                                        if ("" != excelValue.ToString())//出现空，但部位DBNull的情况
                                        {
                                            //如果是包含百分号%的double，强制转换为数字
                                            if (info.PropertyType.ToString() == "System.Double")
                                            {
                                                object dbValue = Convert.ChangeType(processNum(excelValue.ToString()), info.PropertyType);
                                                info.SetValue(model, dbValue, null);
                                            }
                                            else
                                            {
                                                object dbValue = Convert.ChangeType(excelValue, info.PropertyType);
                                                info.SetValue(model, dbValue, null);
                                            }
                                        }
                                        else
                                        {
                                            info.SetValue(model, null, null);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    if (db.Insert(model) != null)
                        row++;
                }
                trans.Complete();
            }
            return row;
        }

        public decimal processNum(string text)
        {
            try
            {
                string valText = text;
                valText = valText.Replace("%", "");
                valText = valText.Replace("￥", "");

                decimal percNum = Decimal.Parse(valText);

                if (text.Contains("%"))
                    return percNum / (decimal)100;
                else
                    return percNum;
            }
            catch
            {
                return 0;
            }
        }

        //获取所有店铺
        public List<shop> GetShops()
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Fetch<shop>("select * from conf_shop order by type");
            }
        }

        //新增店铺
        public int AddShop(string shopName, string url)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Execute("INSERT INTO conf_shop(name, url) values(@0, @1)", shopName, url);
            }
        }

        //获取店铺
        public shop GetShop(string shopName)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.SingleOrDefault<shop>("SELECT * FROM conf_shop where name=@0", shopName);
            }
        }

        //获取某个报表数据（通用）
        public DataTable GetReports(string tableName, int shopid, DateTime beginTime, DateTime endTime)
        {
            string bt = string.Format("{0:yyyy-MM-dd HH:mm:ss}", beginTime);
            string et = string.Format("{0:yyyy-MM-dd HH:mm:ss}", endTime);
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                DataTable table = db.ExecuteTable(string.Format("SELECT * FROM {0} WHERE shopid={1} AND stat_datetime>='{2}' AND stat_datetime<='{3}' ORDER BY stat_datetime", tableName, shopid, bt, et));
                return table;
            }
        }
        //获取某个店铺已导入文件列表;
        public DataTable GetFileImports(int shopid, string fileType)
        {
            string sql = "";
            if (fileType == "--所有--")
                sql = string.Format("SELECT * FROM file_import WHERE shopid={0} ORDER BY filename DESC", shopid);
            else
                sql = string.Format("SELECT * FROM file_import WHERE shopid={0} AND filetype='{1}' ORDER BY filename DESC", shopid, fileType);
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                DataTable table = db.ExecuteTable(sql);
                return table;
            }
        }

        //从某个表中删除已导入文件的相关数据;
        public void CleanData(int fileid, string tablename)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                db.Execute(string.Format("DELETE FROM {0} WHERE fileid={1}", tablename, fileid));
                db.Execute(string.Format("DELETE FROM file_import WHERE fileid={0}", fileid));
            }
        }


        public PetaPoco.Page<T> GetReports<T>(int shopid, DateTime beginTime, DateTime endTime, long page, long itemsPerPage, string SortFields)
        {
            string bt = string.Format("{0:yyyy-MM-dd}", beginTime) + " 00:00:00";
            string et = string.Format("{0:yyyy-MM-dd}", endTime) + " 23:59:59";
            Type type = typeof(T);
            string tableName = type.Name;
            string sql = string.Format("SELECT * FROM {0} WHERE shopid={1} AND stat_datetime>='{2}' AND stat_datetime<='{3}' ORDER BY {4}", tableName, shopid, bt, et, SortFields);

            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Page<T>(page, itemsPerPage, sql);
            }
        }

        public Type GetTypeByTable(string tableName)
        {
            Assembly asmb = Assembly.LoadFrom("OneMake.Domain.dll");
            return asmb.GetType(string.Format("OneMake.Model.{0}", tableName));
        }

        //获取字段名称-中文意思的映射
        public Dictionary<string, string> GetTableFieldMap(string tableName)
        {
            Dictionary<string, string> attrMap = new Dictionary<string, string>();

            Assembly asmb = Assembly.LoadFrom("OneMake.Domain.dll");
            Type modelType = asmb.GetType(string.Format("OneMake.Model.{0}", tableName));
            object model = asmb.CreateInstance(string.Format("OneMake.Model.{0}", tableName));

            foreach (PropertyInfo info in modelType.GetProperties())
            {
                foreach (Attribute attr in info.GetCustomAttributes(true))
                {
                    MapToExcelAttribute mapToAttr = attr as MapToExcelAttribute;
                    if (null != mapToAttr)
                    {
                        attrMap.Add(info.Name, mapToAttr.ColumnName);
                    }
                    else
                    {
                        MapToFieldAttribute mapToAttr2 = attr as MapToFieldAttribute;
                        if (null != mapToAttr2)
                        {
                            attrMap.Add(info.Name, mapToAttr2.FieldName);
                        }
                    }
                }

            }

            return attrMap;
        }

        //文件是否已经导入过
        public bool IsFileExist(int shopid, string fileName)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                file_import fi = db.FirstOrDefault<file_import>("select * from file_import where filename=@0 AND shopid=@1", fileName, shopid);
                return (fi != null) ? true : false;
            }
        }
        //新增一个文件信息
        public object AddFileInfo(file_import fi)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Insert(fi);
            }
        }

        //重复导入文件更新
        public int UpdateFileInfo(file_import fi)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Update(fi);
            }
        }

        //获取所有图片类型信息
        public List<conf_ad_type> GetAdType()
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Fetch<conf_ad_type>("select * from conf_ad_type");
            }
        }
        //获取所有图片位置信息
        public List<conf_ad_position> GetAdPosition()
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Fetch<conf_ad_position>("select * from conf_ad_position");
            }
        }

        //获取所有店铺
        public List<employee_info> GetEmployees()
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                return db.Fetch<employee_info>("select * from employee_info order by employee_no");
            }
        }

        public void WriteLog(int level, int code, string msg)
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                db.Execute("INSERT INTO log_operation(log_datetime, log_level, log_code, log_msg, employeeid) values(now(), @0, @1, @2, @3)",
                    level, code, msg, UserSession.EmployeeId);

                db.Execute("DELETE FROM log_operation WHERE log_datetime<DATE_SUB(Now(), INTERVAL 30 DAY)");
            }
        }

        //获取某个店铺已导入文件列表;
        public DataTable GetLogs(int employeeid, DateTime beginTime, DateTime endTime)
        {
            string bt = string.Format("{0:yyyy-MM-dd}", beginTime) + " 00:00:00";
            string et = string.Format("{0:yyyy-MM-dd}", endTime) + " 23:59:59";
            string sql = string.Format("SELECT oid,employeeid,log_datetime, log_level, log_code, log_msg FROM log_operation WHERE log_datetime>'{0}' AND log_datetime<'{1}' AND employeeid={2} ORDER BY oid", bt, et, employeeid);
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                DataTable table = db.ExecuteTable(sql);
                return table;
            }
        }

        public void CleanRubbishData()
        {
            using (var db = new PetaPoco.Database("mysql" + UserSession.Network))
            {
                db.Execute("DELETE FROM ct_d_cgldb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_comment WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_gzldb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_jxdb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_kdjdb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_kjsdb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_order WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_quire WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_tkqkdb WHERE ww='' OR ww IS NULL");
                db.Execute("DELETE FROM ct_d_kfghzb WHERE ww='' OR ww IS NULL");

                db.Execute("DELETE FROM hdp_d_deal WHERE buyer_nick='' OR buyer_nick IS NULL");
                db.Execute("DELETE FROM htjy_d_products WHERE order_title='' OR order_title IS NULL");
                db.Execute("DELETE FROM htjy_d_orders WHERE order_sn='' OR order_sn IS NULL");
                db.Execute("DELETE FROM lz_d_bdetail WHERE member='' OR member IS NULL");

                db.Execute("DELETE FROM lz_d_shopop WHERE stat_hour='' OR stat_hour IS NULL");
                db.Execute("DELETE FROM lz_m_shopop WHERE stat_hour<'2000-01-01'");

                db.Execute("DELETE FROM jhs_d_yxhd WHERE shopname='' OR shopname IS NULL");

                db.Execute("DELETE FROM mf_m_tz WHERE hotsale_feature='' OR hotsale_feature IS NULL");
            }
        }
    }
}
